import mariadb
import sys
import pandas as pd

# Connect to MariaDB Platform
try:
    conn = mariadb.connect(
        user="root",
        password="like",
        host="192.168.0.100",
        port=3306,
        database="zlib"

    )
    print('connect zlib datebase!!!')
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

# Get Cursor
cur = conn.cursor()

dpath = r'e:\C\aDrive\Downloaded\pilimi-zlib-index-2022-08-24\\'

efiles = []

with open(dpath+'all.txt', 'r', encoding='utf-8') as f:
    efiles = [line.rstrip('\n') for line in f.readlines()]

for excel_file in efiles:
    dataframe = pd.read_excel(excel_file)

    for row in dataframe.iterrows():
        index = row[1]['文件编号']
        udate = row[1]['上传日期'].strftime('%Y-%m-%d %X')
        date1 = row[1]['日期1'].strftime('%Y-%m-%d %X')
        fformat = row[1][3]
        fsize = row[1][4]
        fname = row[1]['书名'] 
        fauthor = row[1]['作者']
        fpublish = row[1]['出版社']
        language = row[1]['语种']
        pyear = row[1]['出版年份'] 
        ppage = row[1]['页码']
        torrent = row[1]['种子文件']

        sql = f"insert into books values ({index}, '{udate}', '{date1}', '{fformat}', \
                 '{fsize}', '{fname}', '{fauthor}', '{fpublish}', '{language}', '{pyear}', '{ppage}', '{torrent}')"
        try:
            # print(sql)
            cur.execute(sql)
            print('insert ok', index)
            conn.commit()
        except mariadb.Error as e:
            print('insert db error:', e)

conn.close()
